using System;
using System.Data;
using System.Data.SQLite;

/*
	Jun 2012: Added the nullable field info pointer 
*/
namespace SqliteDriver
{
	/// <summary>
	/// Summary description for Helper.
	/// </summary>
	internal class Helper
	{
		public Helper() { }

		public static String MakeSearchClause(String seek)
		{
			String clause = String.Format("like \'%{0}%\'", seek.Trim());
			if (seek.Length == 0)
				return clause;
			// get beginning and end chars
			char first = seek[0];
			char last = seek[seek.Length - 1];
			// use regex 
			if (first == '^' && last == '$')
				return String.Format("= '{0}'", seek.Substring(1, seek.Length - 2));
			// assume it will be "like '%xxx%'
			if (first == '^')
				return String.Format("like \'{0}%\'", seek.Substring(1, seek.Length - 1).Trim());
			else
				// assume it will be "like '%xxx%'
				if (last == '$')
					return String.Format("like \'%{0}\'", seek.Substring(0, seek.Length - 1));
			return clause;
		}

		public static String SafeSqlField(String field)
		{
			if (field.Length == 0)
				return field;
			if (field[0] == '[' && field[field.Length - 1] == ']')
				return field;
			// hacked fix for aliased fields.
			if (field.IndexOf('.') > 0)
				return field;
			return "[" + field.Trim() + "]"; // does not allow for fields with leading & trailing spaces
		}

		public static String SafeTableName(String tableName)
		{
			// empty string: reflect it back
			if (tableName.Length == 0) return tableName;
			// already escaped apparently: nothing to do
			if (tableName[0] == '[' && tableName[tableName.Length - 1] == ']')
				return tableName;
			// 
			// Experimental: if a sub query then skip
			//
			if (tableName.Replace(" ", "").Replace("\n", "").Replace("\r", "").ToUpper().IndexOf("(SELECT") != -1) return tableName;
			// no schema: just [ tablename ]
			if (tableName.IndexOf('.') == -1)
				return "[" + tableName + "]";
			// Schema.tablename -> [Schema].[tablename]
			return "[" + tableName.Replace(".", "].[") + "]";
		}

		public static string SafeData(String dataValue)
		{
			return dataValue.Replace("'", "''");
		}

		internal static bool TableIsSubQuery(string tableName)
		{
			// to test whether the string is a true table (99.999% of the time)
			// or a subquery in a custom view
			// SafeTableName() adds leading and trailing bracket to a real table startin with (
			// remove any leading whitespace and check for (
			if (SafeTableName(tableName).Replace(" ", "").IndexOf('(') == 0)
				return true;
			return false;
		}
		public static DataTable GenericQuery(String strConnectionString, String sql, String strTableName)
		{
			DataTable dt = new DataTable();
			SQLiteConnection cnn = null;
			try
			{
				cnn = new SQLiteConnection(strConnectionString);
				cnn.Open();
				SQLiteCommand mycommand = new SQLiteCommand(cnn);
				mycommand.CommandText = sql;
				SQLiteDataReader reader = mycommand.ExecuteReader();
				dt.Load(reader);
				reader.Close();
				cnn.Close();
			}
			catch (Exception e)
			{
				// may not work if connection fails
				cnn.Close();
				String message = String.Format("Sql Error: {0}\n" +
											   "connection: {1}\n" +
												"Sql: {2}\n",
												e.Message, strConnectionString, sql);
				Exception ex = new Exception(message);
				throw ex; // abort process having logged as much as possible.
				throw new Exception(e.Message);
			}
			return dt;
		}

		public static void GenericMakeDset(String strConnectionString, String sql, String strTableName, out DataSet dset)
		{
		    // Create a data reading object to fill the dataset
		    dset = new DataSet();
		    dset.Tables.Add(GenericQuery(strConnectionString, sql, strTableName));
		}



		// don't like this.
		//public static void GenericMakeDset(String strConnectionString, String sql, String strTableName, out DataSet dset)
		//{
		//    // Create a data reading object to fill the dataset
		//    dset = new DataSet();
		//    SQLiteConnection sqlConn = new SQLiteConnection();
		//    try
		//    {
		//        // get the connection 
		//        sqlConn.ConnectionString = strConnectionString;
		//        sqlConn.Open();
		//        using (SQLiteCommand cmd = new SQLiteCommand(sql, sqlConn))
		//        {
		//            SQLiteDataReader da = cmd.ExecuteReader();
		//            while (true)
		//            {
		//               DataTable myTable = new DataTable();
		//               myTable.Load(da);
		//               dset.Tables.Add(myTable);
		//               if (da.IsClosed)
		//                 break;
		//            } 
		//        }
 
		//        sqlConn.Close();

		//    }
		//    catch (SQLiteException e)
		//    {
		//        // may not work if connection fails
		//        sqlConn.Close();
		//        String message = String.Format("Sql Error: {0}\n" +
		//                                       "connection: {1}\n" +
		//                                        "Sql: {2}\n",
		//                                        e.Message, Depassword(strConnectionString), sql);
		//        Exception ex = new Exception(message);
		//        throw ex; // abort process having logged as much as possible.
		//    }
		//}

		// Has knowledge of SqlServerDataSource.MakeConnectionString()
		private static String Depassword(string strConnectionString)
		{
			// knows this is the password tag and that it's the last item in the connection String
			int pos = strConnectionString.IndexOf(";password=");
			if (pos == -1) return strConnectionString; // should be Trusted connection in which case no password info is displayed.
			return strConnectionString.Substring(0, pos); // cut-off the password information
		}

		// simpler version. Only for a query returning a datatable
		public static DataTable GenericQuery(String strConnectionString, String sql)
		{
			DataSet dset;
			GenericMakeDset(strConnectionString, sql, "DATA", out dset);
			return dset.Tables[0];
		}

		public static String GenericScalarQuery(String strConnectionString, String sql, String defaultIfEmpty)
		{
			DataSet dset;
			GenericMakeDset(strConnectionString, sql, "DATA", out dset);
			DataTable dt = dset.Tables[0];
			// very loose interpretation of duff results.
			if (dt == null) return defaultIfEmpty;
			if (dt.Rows.Count == 0) return defaultIfEmpty;
			if (dt.Rows[0][0] == System.DBNull.Value) return defaultIfEmpty;
			return dt.Rows[0][0].ToString();
		}

		// Compatibility
		public static String GenericExecute(String strConnectionString, String sql)
		{
			bool succeeded;
			return GenericExecute(strConnectionString, sql, out succeeded);
		}

		public static String GenericExecute(String strConnectionString, String sql, out bool succeeded)
		{
			String returnMessage = "";
			succeeded = true; // assume it was OK

			SQLiteConnection cnn = new SQLiteConnection(strConnectionString);
			try
			{
				cnn.Open();
				SQLiteCommand mycommand = new SQLiteCommand(cnn);
				mycommand.CommandText = sql;
				int rowsUpdated = mycommand.ExecuteNonQuery();
				returnMessage = "Command completed";
				if (rowsUpdated != -1)
					returnMessage += String.Format("\n{0} rows affected", rowsUpdated);
			}
			catch (Exception e)
			{
				returnMessage = e.Message;
				succeeded = false; // easier check on failure.
			}
			finally
			{
				cnn.Close();
			}
			return returnMessage;
		}

		public static bool TestConnection(String strConnectionString, out String message)
		{
			message = ""; // OK 
			bool isTestedOk = true;
			try
			{
				String testSql = "PRAGMA user_version";
				using (SQLiteConnection sqlConn = new SQLiteConnection(strConnectionString))
				{
					sqlConn.Open();
					SQLiteCommand mycommand = new SQLiteCommand(sqlConn);
					mycommand.CommandText = testSql;
					object o = mycommand.ExecuteScalar();
					sqlConn.Close();
					message = string.Format("Connect tested OK.\n User version = {0}", o.ToString());
				}
			}
			catch (SQLiteException e)
			{
			    message = String.Format("{0}", e.Message);
				if (strConnectionString.ToUpper().Contains("PASSWORD"))
					message += "\n\n..or you have set a password on a non-protected database";
			    // may not work if connection fails
			    isTestedOk = false;
			}
			return isTestedOk;
		}

		public static bool ResetPassword(String strConnectionString, string newPassword)
		{
			using (SQLiteConnection cnn = new SQLiteConnection(strConnectionString))
			{
				cnn.Open();
				cnn.ChangePassword(newPassword);
				cnn.Close();
			}
			return true;
		}


		public static String DocFromDset(String strConnectionString, String sql, int sourceCol, bool addNewLine)
		{
			DataTable sourceLines = GenericQuery(strConnectionString, sql);

			// use stringbuilder? Nah! Only ever 1 or 2 rows 
			String sourceCode = "";
			foreach (DataRow r in sourceLines.Rows)
			{
				sourceCode += r[sourceCol].ToString();
				if (addNewLine)
					sourceCode += "\n";
			}
			return sourceCode;
		}

		public static String GetSourceCode(String strConnectionString, String strSelectedObject)
		{
			String searchTemplate = @"
SELECT name, sql 
  FROM sqlite_master 
WHERE name = '{0}'
";
			String sql = String.Format(searchTemplate, strSelectedObject);

			return DocFromDset(strConnectionString, sql, 1, false);
		}

		/*
			Breaks a table name into schema table
			but reverses the order so that the table name is always 1st.
		*/
		public static String[] ParseTableName(String tableName)
		{
			String[] result = tableName.Split('.');
			if (result.GetLength(0) > 1)
			{
				String tmp = result[1];
				result[1] = result[0];
				result[0] = tmp;
			}
			return result;
		}

		//
		// new: from Sql datasource
		// 
		public static SqliteTemplates.Version DetermineSqlVersion(String connStr)
		{
			return SqliteTemplates.Version.Three;
		}

		//
		// adapted from SqlServerDataSource version. Requires additional server call for version so slower.
		//
		public static SqlTableSchema MakeSchemaFor(String tableName, String connStr)
		{
			// look up
			//SqliteTemplates.Version sqlVersion = DetermineSqlVersion(connStr);
			//String commandTemplate = SqliteTemplates.FetchTemplate(sqlVersion, DbView.Commands.Cmnd.Fields);
			//// note: false is case-sensitive parameter, but that only changes code when the filter text is not
			//// empty. In this case it is empty so this should work for case sensitive databases as well.
			//commandTemplate = SqliteTemplates.Templates.ProcessFilterClause(commandTemplate, "", false);
			//String commandString = String.Format(commandTemplate, tableName);
			//DataSet dataset = new DataSet();
			//Helper.GenericMakeDset(connStr, commandString, tableName, out dataset);
			//DataTable schemaTable = dataset.Tables[0];
			// TODO:
			//schemaTable.TableName = tableName;
			DataTable schemaTable = SqlTableSchema.MakeFieldInfoTable(connStr, tableName);
			SqlTableSchema schema = new SqlTableSchema();
			schema.Populate(schemaTable, "Field Name", "Type", "Length", "Dp", "PKey?", "Auto?",
										 "FKey?", "Index?", "Calc?", "Def?", "Default/Computed", "nulls?");

			return schema;
		}

		// Imported from the application assembly. TODO move it to the library
		/* as you always forget
			dt.Rows.Add(new object[] { 1, "<clear filter>", "" });
		*/
		public static DataTable MakeDataTable(String name, String columnNames, String columnTypes)
		{
			String[] colNames = columnNames.Split('|');
			if (colNames.GetLength(0) != columnTypes.Length)
			{
				int n1 = colNames.GetLength(0);
				int n2 = columnTypes.Length;
				String msg = String.Format("Invalid table specification for MakeDataTable\n" +
										   "The number of columns must equal the number of column type characters.\n" +
										   "There are {0} names in {1}\n" +
										   "There are {2} chars in {3}", n1, columnNames, n2, columnTypes);
				throw new Exception(msg);
			}
			DataTable dt = new DataTable(name);
			for (int idx = 0; idx < colNames.GetLength(0); ++idx)
			{
				String colName = colNames[idx];
				String typeName = Helper.ResolveType(columnTypes[idx]);

				DataColumn col = new DataColumn();
				col.ColumnName = colName;
				col.DataType = System.Type.GetType(typeName);
				dt.Columns.Add(col);
			}
			return dt;
		}

		public static DataTable MakeSortedTable(DataTable source, String field, bool asc)
		{
			DataTable target = source.Clone(); // copy structure so rows can be received
			String sortClause = String.Format("{0} {1}", field, (!asc) ? "DESC" : "");
			DataRow[] sorted = source.Select("", sortClause);
			foreach (DataRow sortedRow in sorted)
				target.ImportRow(sortedRow);
			return target;
		}

		private static String ResolveType(char c)
		{
			switch (c)
			{
				case 'S': return "System.String";
				case 'I': return "System.Int32";
				case 'J': return "System.Int64";  // j = I++
				case 'N': return "System.Double";
				case 'D': return "System.DateTime";
				default:
					throw new Exception(String.Format("Invalid Type code {0} - expecting S,I,J, N,D", c));
			}
		}
	}
}
